# from envs.glj.Lib.email.policy import default
from flask_sqlalchemy import SQLAlchemy
from datetime import date

db = SQLAlchemy()


# 管理员模型
class Admin(db.Model):
    __tablename__ = 'admins'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    admin_no = db.Column(db.String(20), unique=True, nullable=False)
    password = db.Column(db.String(120), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=True)
    telephone = db.Column(db.String(20), nullable=True)
    address = db.Column(db.String(120), nullable=True)


# 学生模型
class Student(db.Model):
    __tablename__ = 'students'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    student_no = db.Column(db.String(20), unique=True, nullable=False)
    age = db.Column(db.Integer, nullable=True)
    password = db.Column(db.String(120), nullable=False)
    telephone = db.Column(db.String(20), nullable=True)
    address = db.Column(db.String(120), nullable=True)
    gender = db.Column(db.String(10))
    major = db.Column(db.String(50))
    status = db.Column(db.String(10), nullable=True)


    # 所属班级ID（删除班级时，学生数据应保留但班级ID设为NULL）
    class_id = db.Column(
        db.Integer,
        db.ForeignKey('classes.id', ondelete='SET NULL'),  # 不级联删除，班级删除时学生班级ID设为NULL
        nullable=True ,
        # default=1,# 允许为空,

    )

    # 关系
    class_ = db.relationship('Classes', backref='students', foreign_keys=[class_id])
    course_selections = db.relationship('CourseSelection', backref='student', lazy=True)


# 教师模型
class Teacher(db.Model):
    __tablename__ = 'teachers'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password = db.Column(db.String(120), nullable=False)
    email = db.Column(db.String(120), unique=False, nullable=True)
    telephone = db.Column(db.String(20), nullable=True)
    address = db.Column(db.String(120), nullable=True)
    teacher_no = db.Column(db.String(20), unique=True, nullable=False)
    title = db.Column(db.String(50))
    department = db.Column(db.String(50), nullable=False, default="无")
    hire_date = db.Column(db.Date)
    courses = db.relationship('Course', backref='teacher', lazy=True)


# 班级模型
class Classes(db.Model):
    __tablename__ = 'classes'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    class_name = db.Column(db.String(80), unique=True, nullable=False)
    major = db.Column(db.String(50), nullable=False)
    grade = db.Column(db.Integer, nullable=False)
    description = db.Column(db.String(120), nullable=True)
    # 班长ID（删除学生时，班级数据保留，班长ID设为NULL）
    monitor_id = db.Column(
        db.Integer,
        db.ForeignKey('students.id', ondelete='SET NULL'),  # 不级联删除，学生删除时班长ID设为NULL
        nullable=True
    )
    # 班主任ID（删除教师时，班级数据保留，班主任ID设为NULL）
    advisor_id = db.Column(
        db.Integer,
        db.ForeignKey('teachers.id', ondelete='SET NULL'),  # 不级联删除，教师删除时班主任ID设为NULL
        nullable=True,
        unique=True
    )
    # 关系
    monitor = db.relationship('Student', foreign_keys=[monitor_id])
    advisor = db.relationship('Teacher', foreign_keys=[advisor_id],backref=db.backref('classes', lazy=True))


# 课程模型
class Course(db.Model):
    __tablename__ = 'courses'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    course_code = db.Column(db.String(20), unique=True, nullable=False)
    course_name = db.Column(db.String(80), nullable=False)
    credit = db.Column(db.Integer, nullable=False)   #课程学分（如 4 学分）
    course_type = db.Column(db.String(20), nullable=False)
    department = db.Column(db.String(50), nullable=False) #课程所属院系（如 计算机学院）
    # 授课教师ID（删除教师时，禁止删除课程，需先重新分配教师）
    teacher_id = db.Column(
        db.Integer,
        db.ForeignKey('teachers.id', ondelete='RESTRICT'),  # 不级联删除，禁止删除有课程的教师
        nullable=False
    )
    location = db.Column(db.String(50), nullable=False)
    time=db.Column(db.DateTime, nullable=False)
    description = db.Column(db.Text)




# 选课模型
class CourseSelection(db.Model):
    __tablename__ = 'course_selections'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 学生ID（删除学生时，选课记录保留但学生ID设为NULL）
    student_id = db.Column(
        db.Integer,
        db.ForeignKey('students.id', ondelete='SET NULL'),  # 不级联删除，学生删除时选课记录保留
        nullable=True
    )
    # 课程ID（删除课程时，选课记录保留但课程ID设为NULL）
    course_id = db.Column(
        db.Integer,
        db.ForeignKey('courses.id', ondelete='SET NULL'),  # 不级联删除，课程删除时选课记录保留
        nullable=True
    )
    courses=db.relationship('Course', backref='course_selections', lazy=True)
    selection_date = db.Column(db.DateTime, default=db.func.current_timestamp())
    status = db.Column(db.String(20), default='enrolled')
